<?php

/**
 * This is the model class for table "sh_spends".
 *
 * The followings are the available columns in table 'sh_spends':
 * @property integer $id
 * @property string $name
 * @property string $note
 * @property string $time
 * @property integer $cat_id
 * @property integer $sub_cat_id
 * @property string $reference
 * @property integer $value
 * @property integer $type
 */
class Spend extends CActiveRecord
{
    /**
     * Returns the static model of the specified AR class.
     * @param string $className active record class name.
     * @return Spends the static model class
     */

    public $time_up_from;
    public $time_up_to;

    public static function model($className = __CLASS__)
    {
        return parent::model($className);
    }

    /**
     * @return string the associated database table name
     */
    public function tableName()
    {
        return 'sh_spends';
    }

    /**
     * @return array validation rules for model attributes.
     */
    public function rules()
    {
        // NOTE: you should only define rules for those attributes that
        // will receive user inputs.
        return array(
            array('name, value, cat_id', 'required'),
            array('value', 'numerical', 'integerOnly' => true),
            array('name, note, reference, time', 'length', 'max' => 255),
            // The following rule is used by search().
            // Please remove those attributes that should not be searched.
            array('id, name, note, reference,time, value', 'safe', 'on' => 'search'),
        );
    }

    /**
     * @return array relational rules.
     */
    public function relations()
    {
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array();
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels()
    {
        return array(
            'id' => 'ID',
            'name' => 'Name',
            'note' => 'Note',
            'cat_id' => 'Danh mục',
            'sub_cat_id' => 'Danh mục phụ',
            'time' => 'Ngày giờ',
            'reference' => 'Reference',
            'value' => 'Value',
            'type' => 'Type',
        );
    }

    /**
     * Retrieves a list of models based on the current search/filter conditions.
     * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
     */
    public function search()
    {
        // Warning: Please modify the following code to remove attributes that
        // should not be searched.

        $criteria = new CDbCriteria;

        $criteria->compare('id', $this->id);
        $criteria->compare('name', $this->name, true);
        $criteria->compare('note', $this->note, true);
        $criteria->compare('cat_id', $this->cat_id, true);
        $criteria->compare('sub_cat_id', $this->sub_cat_id, true);
        $criteria->compare('time', $this->time, true);
        $criteria->compare('reference', $this->reference, true);
        $criteria->compare('value', $this->value);
        $criteria->compare('type', $this->type);


        $pagination = new CPagination();
        $pagination->pageSize = 1000;
        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
            'pagination' => $pagination,
        ));
    }

    /**
     * Retrieves a list of models based on the current search/filter conditions.
     * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
     */
    public function user_search()
    {
        // Warning: Please modify the following code to remove attributes that
        // should not be searched.

        $criteria = new CDbCriteria;
        $criteria->compare('time', '>=' . $this->time_up_from);
        $criteria->compare('time', '<=' . $this->time_up_to);
        $criteria->compare('id', $this->id);
        $criteria->compare('name', $this->name, true);
        $criteria->compare('note', $this->note, true);
        $criteria->compare('cat_id', $this->cat_id, true);
        $criteria->compare('sub_cat_id', $this->sub_cat_id, true);
        $criteria->compare('reference', $this->reference, true);
        $criteria->compare('value', $this->value);
        $criteria->compare('type', $this->type);


        $pagination = new CPagination();
        $pagination->pageSize = 1000;
        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
            'pagination' => $pagination,
        ));
    }


    /**
     * Ham tinh tong so tien tieu trong 1 cot
     * @author : SonHA
     * @date : 1/5/2013
     * @param $provider
     * @return int
     */
    public function totalMoney($provider)
    {
        $total = 0;
        foreach ($provider->data as $item)
            $total += $item->value;
        return $total;
    }

    /**
     * Ham tinh so tien tieu trong mot khoang thoi gian
     * @author : SonHA
     * @date : 24/4/2013
     * @param $from
     * @param $to
     */
    public static function getSpendMoneyByRange($from, $to)
    {
        $spend_money = Yii::app()->db->createCommand()
            ->select('sum(value) as total')
            ->from('sh_spends')
            ->where('time >=' . $from . ' AND time <= ' . $to)
            ->queryScalar();

        return $spend_money;
    }

    /**
     * Ham lay tong so tien tieu trong 1 ngay
     * @param $time
     * @return mixed
     */
    public static function getSpendMoneyByDay($time)
    {
        $day_from_time = date('Y-m-d 00:00:00', $time);
        $start_time = strtotime($day_from_time);
        $end_time = $start_time + 86399;
        $income_money = Yii::app()->db->createCommand()
            ->select('sum(value) as total')
            ->from('sh_spends')
            ->where('time >=' . $start_time . ' AND time <= ' . $end_time)
            ->queryScalar();
        return $income_money;
    }

    /**
     * Ham lay tong so tien tieu theo tung loai, dua vao khoang thoi gian
     * @param $cat_id
     * @param $from
     * @param $to
     * @return mixed
     */
    public static function getSpendByCategory($cat_id, $from, $to)
    {
        $spend_money = Yii::app()->db->createCommand()
            ->select('sum(value) as total')
            ->from('sh_spends')
            ->where('cat_id = '.$cat_id.' AND time >=' . $from . ' AND time <= ' . $to)
            ->queryScalar();
        return $spend_money;
    }

    /**
     * Ham lay tong so tien hien dang cho vay
     * @param $cat_id
     * @param $from
     * @param $to
     * @return mixed
     */
    public static function getLend()
    {
        $spend_money = Yii::app()->db->createCommand()
            ->select('name, note, value, reference')
            ->from('sh_spends')
            ->where('sub_cat_id = 22')
            ->queryAll();
        return $spend_money;
    }

    /**
     * Ham lay tong so tien hien dang di vay
     * @param $cat_id
     * @param $from
     * @param $to
     * @return mixed
     */
    public static function getLoan()
    {
        $spend_money = Yii::app()->db->createCommand()
            ->select('name, note, value, reference')
            ->from('sh_spends')
            ->where('sub_cat_id = 21')
            ->queryAll();
        return $spend_money;
    }
}